ALTER PROC SP_UPDATE_BUILD_FORECAST_DETAILS ( @SALES_PART_NUMBER VARCHAR(50),
					@CUSTOMER_NAME VARCHAR(50),
					@BASE_PRODUCT  VARCHAR(50),
					@BUFFER_QTY	   INT,	
					@MONTH		   INT,	
					@QUARTER INT,
					@YEAR INT,
					@QTY INT,
					@PORTING_STATUS_CODE INT OUT )
AS
BEGIN
	DECLARE @PARENT_PRODUCT_ID INT
	DECLARE @PRODUCT_ID INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @CURRENT_QUARTER INT 
	DECLARE @ROW_ID INT
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @ROWCOUNT INT

	SET @PORTING_STATUS_CODE = 0 
	SELECT TOP 1 @PRODUCT_ID = PRODUCT_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE P_MARKETING_PART_NUMBER = @SALES_PART_NUMBER AND IS_Primary_FG = 0 
	IF @PRODUCT_ID IS NULL OR @PRODUCT_ID <= 0 
	BEGIN
		SELECT TOP 1 @PRODUCT_ID = PRODUCT_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE P_MARKETING_PART_NUMBER = @SALES_PART_NUMBER
	END
	IF @PRODUCT_ID IS NULL OR @PRODUCT_ID <= 0 
	BEGIN
		SELECT TOP 1 @PRODUCT_ID = PRODUCT_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PART_NUMBER = @SALES_PART_NUMBER AND P_MARKETING_PART_NUMBER IS NOT NULL
	END

	IF ( @PRODUCT_ID IS NULL OR @PRODUCT_ID <= 0 )
	BEGIN
		SET @PORTING_STATUS_CODE = @PORTING_STATUS_CODE | 1 --FAILED, SALES PART NUMBER NOT FOUND FOR BASE PRODUCT
		RETURN
	END 

	SELECT @ROWCOUNT = COUNT(*) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Reorder Level'
	IF  @ROWCOUNT < 1
	BEGIN
		INSERT INTO PROCESS_PARAMETER VALUES ( @PROCESSSTEP_ID, 'Reorder Level', 'UNITS', @BUFFER_QTY )
	END
	ELSE
	BEGIN
		Update PROCESS_PARAMETER set parameter_Value = @BUFFER_QTY where PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Reorder Level'
	END

	SELECT @CUSTOMER_ID = Customer_ID FROM Customer WHERE C_Name = @CUSTOMER_NAME
	IF ( @CUSTOMER_ID IS NULL OR @CUSTOMER_ID <= 0 )
	BEGIN
		EXEC SP_SAVE_CUSTOMER @CUSTOMER_NAME, @CUSTOMER_NAME, '', '', '', 0, @CUSTOMER_ID OUT, '', '', ''
		SET @PORTING_STATUS_CODE = @PORTING_STATUS_CODE | 2 --NEW CUSTOMER CREATED
	END
	
	SELECT @CURRENT_QUARTER = CQC_QUARTER_NO FROM Company_Quarterly_Calendar WHERE CONVERT( VARCHAR, CURRENT_TIMESTAMP ) >= CQC_START_DATE AND CONVERT( VARCHAR, CURRENT_TIMESTAMP ) <= CQC_END_DATE
		
	IF @QTY IS NOT NULL AND @QTY > 0
	BEGIN 
		INSERT INTO [FCBUILDMASTER]( [ProdId],[CustId],[Month], [Quarter],[Year],[Qty],[UserId],[EntryDate] )
		VALUES ( @PRODUCT_ID, @CUSTOMER_ID, @MONTH, @QUARTER, @YEAR, @QTY, 'Admin', CURRENT_TIMESTAMP )
		SELECT @ROW_ID = @@IDENTITY
		INSERT INTO [FCBUILDHISTORY]( [RowId],[ProdId],[CustId],[Month],[Quarter],[Year],[Qty],[UserId],[EntryDate] )
		VALUES ( @ROW_ID, @PRODUCT_ID, @CUSTOMER_ID, @MONTH, @QUARTER, @YEAR, @QTY, 'Admin', CURRENT_TIMESTAMP )
	END
END